﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class HrmPositionDAL
    {
        public HrmPositionDAL()
        {

        }
        public DataSet LoadPositionUpper(Nullable<int> PositionSequence, Nullable<int> EmployeeSequence)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", PositionSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmployeeSequence);
                //dbManager.AddParameters(2, "@JobSite", "%/" + jsSeq.ToString() + "/%");
                //dbManager.AddParameters(3, "@EmpTypeSeq", etSeq);
                //dbManager.AddParameters(4, "@PosName", "%/" + posInfo.Name + "/%");

                strSQL = "SELECT * FROM("
                    + " SELECT "
                    + " FROM HRM_MP_POSITIONS p1"
                    + " LEFT JOIN HRM_MP_POSITIONS p2 ON p1.POS_UPPER_SEQ = p2.POS_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p3 ON p2.POS_UPPER_SEQ = p3.POS_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p4 ON p3.POS_UPPER_SEQ = p4.POS_SEQ"

                    + " LEFT JOIN HRM_PS_EMPLOYEES e1 ON p1.POS_SEQ = e1.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e2 ON p2.POS_SEQ = e2.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e3 ON p3.POS_SEQ = e3.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e4 ON p4.POS_SEQ = e4.POS_SEQ"
                    + ")t";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return null;
        }
    }
}
